#include "AdDao.h"
#include <sqlite3.h>
#include <stdlib.h>
#include <stdio.h>
#include "glib.h"

int saveAd(Ad* ad){
    sqlite3* db=NULL;
    //打开
    int retVal=sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db error");
        return -1;
    }
    char sql[256];
    sprintf(sql,"insert into t_ad(id,`index`,start_time,end_time) values(%d,%d,'%s','%s');",ad->id,ad->index,ad->start_time,ad->end_time);
    printf("insert ad sql:%s\n",sql);
    char* errorMsg;
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    {
        printf("%s\n",errorMsg);
        return -1;
    }else
    {
        printf("insert ad success\n");
    }
    sqlite3_close(db);
    return 0;
    
}
int updateAd(Ad* ad){
    sqlite3* db=NULL;
    int retVal=sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db error");
        return -1;
    }
    char sql[256];
    sprintf(sql,"update t_ad set `index`=%d,start_time='%s',end_time='%s';",ad->index,ad->start_time,ad->end_time);
    char* errorMsg;
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    {
        printf("%s\n",errorMsg);
        return -1;
    }
    sqlite3_close(db);
    return 0;
}
Ad* getAd(int id){
    sqlite3* db=NULL;
    int retVal=sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db error");
        return NULL;
    }
    char* errorMsg;
    char** pResult;
    int nRow;
    int nCol;
    char sql[256];
    sprintf(sql,"select * from t_ad where id=%d;",id);
    retVal=sqlite3_get_table(db,sql,&pResult,&nRow,&nCol,&errorMsg);
    if (retVal!=SQLITE_OK)
    {
        printf("%s\n",errorMsg);
    }
    int index=nCol;
    printf("查到%d条记录\n",nRow);

    for (int i = 0; i < nRow; i++)
    {
        Ad* ad=calloc(1,sizeof(Ad));
        for (int j = 0; j < nCol; j++)
        {
            char* colName=pResult[j];
            char* colVal=pResult[index];
            if (g_str_equal("id",colName))
            {
                ad->id=atoi(colVal);
            }
            if (g_str_equal("index",colName))
            {
                ad->index=atoi(colVal);
            }
            if (g_str_equal("start_time",colName))
            {
                ad->start_time=colVal;
            }
            if (g_str_equal("end_time",colName))
            {
                ad->end_time=colVal;
            }
            ++index;
        }
        return ad;
    }
    return NULL;
}

int saveAr(AdResource* ar){
    sqlite3* db=NULL;
    int retVal=sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db error");
        return -1;
    }
    char sql[256];
    sprintf(sql,"insert into t_ad_resource(url,`index`,ad_id,sign,type_id) values('%s',%d,%d,'%s',%d);",ar->path,ar->index,ar->ad_id,ar->sign,ar->type_id);
    g_print("%s\n",sql);
    char* errorMsg;
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    {
        printf("%s\n",errorMsg);
        return -1;
    }else
    {
        printf("insert adresource success\n");
    }
    sqlite3_close(db);
    return 0;
}
int updateAr(AdResource* ar){
    sqlite3* db=NULL;
    int retVal=sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db error");
        return -1;
    }
    char sql[256];
    sprintf(sql,"update t_ad_resource set `index`=%d,url='%s',sign='%s',ad_id=%d,type_id=%d where id = %d;",ar->index,ar->path,ar->sign,ar->ad_id,ar->type_id,ar->id);
    char* errorMsg;
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    {
        printf("%s\n",errorMsg);
        return -1;
    }
    sqlite3_close(db);
    return 0;
}
AdResource* getAr(int adid,int indexTmp){
    sqlite3 *db = NULL;
    //打开
    int retVal = sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db  error!");
        return NULL;
    }

    char *errorMsg;   
    char** pResult;  
    int nRow;
    int nCol;
    char sql[256];
    sprintf(sql,"select * from t_ad_resource where ad_id = %d and `index`= %d;",adid,indexTmp);
    retVal = sqlite3_get_table(db,sql,&pResult,&nRow,&nCol,&errorMsg);
    if (retVal != SQLITE_OK)
    { 
        printf("%s\n",errorMsg);
    }

    int  index = nCol;
    printf( "查到%d条记录\n", nRow );

    for(  int i = 0; i < nRow ; i++ )
    {
        AdResource *ar = calloc(1,sizeof(Ad));
        for( int j = 0 ; j < nCol; j++ )
        {
            char *colName =  pResult[j];
            char *colVal =  pResult [index];

            if(g_str_equal("id",colName))
            {
                ar->id = atoi(colVal);
            }

            if(g_str_equal("index",colName))
            {
                ar->index = atoi(colVal);
            }
                 
                if (g_str_equal("ad_id",colName))
            {
                ar->ad_id = atoi(colVal);
            }

                if (g_str_equal("type_id",colName))
            {
                ar->type_id = atoi(colVal);
            }
                 
                if (g_str_equal("sign",colName))
            {
                g_print("%s==========================\n",colVal);
                ar->sign = colVal;
            }

            if(g_str_equal("url",colName))
            {
                ar->path = colVal;
            }
            ++index; 
        }
        return ar;
    }
    return NULL;
}

int clearExpireAd(){
    sqlite3* db=NULL;
    int retVal=sqlite3_open(AD_DB_PATH,&db);
    if (retVal!=0)
    {
        perror("open db error");
        return -1;
    }
    char sql[256];
    sprintf(sql,"delete from t_ad where end_time < datetime(CURRENT_TIMESTAMP,'localtime');");
    char* errorMsg;
    //删除
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    //删除广告资源
    if (errorMsg!=NULL)
    {
        printf("%s\n",errorMsg);
        return -1;
    }
    sprintf(sql,"delete from t_ad_resource where ad_id not in (select id from t_ad);");
    //删除资源
    sqlite3_exec(db,sql,NULL,NULL,&errorMsg);
    if (errorMsg!=NULL)
    {
        printf("%s\n",errorMsg);
        return -1;
    }
    sqlite3_close(db);
}